package com.houseman.workFlow.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.houseman.workFlow.bean.Line;
import com.houseman.workFlow.dao.LineDao;
import com.houseman.workFlow.utils.DBUtils;

public class LineDaoImpl implements LineDao{

	/**
	 * 根据graphId获得线条集合
	 * @return
	 * @throws Exception
	 */
	public List<Line> queryLines(int graphId) throws Exception {
		//获得连接
		Connection conn = DBUtils.geConnection();
		
		PreparedStatement ps = conn.prepareStatement("SELECT ID,GRAPHID,LID,TID,GROWTO,TEXT,TX,TY,POINTS,EXECID FROM LINE WHERE GRAPHID=?");
		ps.setInt(1, graphId);
		
		ResultSet rs = ps.executeQuery();
		
		List<Line> ls = new ArrayList<Line>();
		while (rs.next()) {
			Line l = new Line();
			l.setId(rs.getInt("ID"));
			l.setGraphId(rs.getInt("GRAPHID"));
			l.setLid(rs.getInt("LID"));
			l.setTid(rs.getInt("TID"));
			l.setGrowTo(rs.getInt("GROWTO"));
			l.setText(rs.getString("TEXT"));
			l.setTx(rs.getInt("TX"));
			l.setTy(rs.getInt("TY"));
			l.setPoints(rs.getString("POINTS"));
			l.setExeCid(rs.getString("EXECID"));

			ls.add(l);
		}
		
		//关闭连接
		DBUtils.close(conn, ps, rs);
		return ls;
	}
	
	/**
	 * 根据GRAPHID获得线条ID集合
	 * @return
	 * @throws Exception
	 */
	public List<Integer> queryLineIds(int graphId) throws Exception {
		//获得连接
		Connection conn = DBUtils.geConnection();
		
		PreparedStatement ps = conn.prepareStatement("SELECT ID FROM LINE WHERE GRAPHID=?");
		ps.setInt(1, graphId);
		
		ResultSet rs = ps.executeQuery();
		
		List<Integer> ids = new ArrayList<Integer>();
		while (rs.next()) {
			ids.add(rs.getInt("ID"));
		}
		
		//关闭连接
		DBUtils.close(conn, ps, rs);
		return ids;
	}
	
	/**
	 * 保存流程图信息
	 * @param graph
	 * @throws Exception
	 */
	public void savaLine(List<Line> lines, int graphId) throws Exception {
		//获得连接
		Connection conn = DBUtils.geConnection();
		
		PreparedStatement ps = conn.prepareStatement("INSERT INTO LINE(GRAPHID,LID,TID,GROWTO,TEXT,TX,TY,POINTS,EXECID) VALUES(?,?,?,?,?,?,?,?,?)");
		for (int i = 0; i < lines.size(); i++) {
			ps.setInt(1, graphId);
			ps.setInt(2, lines.get(i).getLid());
			ps.setInt(3, lines.get(i).getTid());
			ps.setInt(4, lines.get(i).getGrowTo());
			ps.setString(5, lines.get(i).getText());
			ps.setInt(6, lines.get(i).getTx());
			ps.setInt(7, lines.get(i).getTy());
			ps.setString(8, lines.get(i).getPoints());
			ps.setString(9, lines.get(i).getExeCid());
			
			ps.execute();
		}
		
		DBUtils.close(conn, ps);
	}
	
	/**
	 * 更新流程图信息
	 * @param graph
	 * @throws Exception
	 */
	public void updateLine(List<Line> lines) throws Exception {
		//获得连接
		Connection conn = DBUtils.geConnection();
		
		PreparedStatement ps = conn.prepareStatement("UPDATE LINE SET TEXT=?,TX=?,TY=?,POINTS=?,EXECID=? WHERE ID=?");
		for (int i = 0; i < lines.size(); i++) {
			ps.setString(1, lines.get(i).getText());
			ps.setInt(2, lines.get(i).getTx());
			ps.setInt(3, lines.get(i).getTy());
			ps.setString(4, lines.get(i).getPoints());
			ps.setString(5, lines.get(i).getExeCid());
			ps.setInt(6, lines.get(i).getId());
			
			ps.executeUpdate();
		}
		
		DBUtils.close(conn, ps);
	}
	
	/**
	 * 根据ID删除线条
	 * @param graph
	 * @throws Exception
	 */
	public void deleteLineById(List<Integer> ids) throws Exception {
		//获得连接
		Connection conn = DBUtils.geConnection();
		
		PreparedStatement ps = conn.prepareStatement("DELETE FROM LINE WHERE ID=?");
		for (int i = 0; i < ids.size(); i++) {
			ps.setInt(1, ids.get(i));
			
			ps.executeUpdate();
		}
		
		DBUtils.close(conn, ps);
	}
	
	/**
	 * 根据GRAPHID删除线条
	 * @param graph
	 * @throws Exception
	 */
	public void deleteLineByGraphId(int graphId) throws Exception {
		//获得连接
		Connection conn = DBUtils.geConnection();
		
		PreparedStatement ps = conn.prepareStatement("DELETE FROM LINE WHERE GRAPHID=?");
		ps.setInt(1, graphId);
		
		ps.executeUpdate();
		
		DBUtils.close(conn, ps);}
}
